/*
****************************************************************************************************************************
*/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('PreparePEXIndex') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	PRINT 'Droping procedure PreparePEXIndex'
	DROP PROCEDURE dbo.PreparePEXIndex
END
GO

PRINT 'Creating procedure dbo.PreparePEXIndex'
GO

CREATE PROCEDURE dbo.PreparePEXIndex
(
	@processDate DATETIME = NULL
)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE 
		@currentDate DATETIME
		,@firstDate DATETIME
		,@nextDate DATETIME
		,@traidingDays INT
		
	--set params
	SET @currentDate = dbo.RemoveTime(@processDate)
	SET @firstDate = dateadd(month,datediff(month,0,@currentDate),0)
	SET @nextDate = dateadd(day,1,@currentDate)

	--clear PEXIndex
	DELETE FROM dbo.PEXIndex WHERE IndexDate = @currentDate

	--update PEXIndex
	
	INSERT INTO dbo.PEXIndex
	( 
		PEXIndexId,MarketEntityId,IndexDate,DayIndex,MonthIndex
	)
	SELECT 
		NEWID()
		,MarketEntityId
		,@currentDate
		,(SUM(TA.Price*TA.Volume)/SUM(TA.Volume))
		,0.0
	FROM dbo.TradeActivity TA
	WHERE TA.ActivityDate BETWEEN @firstDate AND @nextDate
	AND TA.[Status] = 2
	GROUP BY TA.MarketEntityId

	SET @traidingDays = 
		(
			SELECT COUNT(*) 
			FROM
				( 
					SELECT dbo.RemoveTime(TA.ActivityDate) AS ActivityDate
					FROM dbo.TradeActivity TA 
					WHERE TA.ActivityDate BETWEEN @firstDate AND @nextDate
					AND TA.[Status] = 2
					GROUP BY dbo.RemoveTime(TA.ActivityDate)
				) AS GrpTrades
		)
	
	IF @traidingDays = 0
	BEGIN
		SET @traidingDays = 1
	END

	UPDATE PE SET MonthIndex = PESum.DayIndex/@traidingDays
	FROM dbo.PEXIndex PE
	JOIN 
	(
		SELECT 
			MarketEntityId,MONTH(SumPE.IndexDate) AS IndexMonth,
			YEAR(SumPE.IndexDate) AS IndexYear,SUM(SumPE.DayIndex) AS DayIndex
		FROM dbo.PEXIndex SumPE
		GROUP BY SumPE.MarketEntityId,MONTH(SumPE.IndexDate),YEAR(SumPE.IndexDate)
	) AS PESum ON PESum.MarketEntityId = PE.MarketEntityId
	WHERE PE.IndexDate = @currentDate 
	AND PESum.IndexYear = YEAR(PE.IndexDate)
	AND PESum.IndexMonth = MONTH(PE.IndexDate)

	SET NOCOUNT OFF
END
GO

